<?php


namespace coco;


use coco\client\Mysql;
use coco\traits\Singleton;

/**
 * 数据库
 * 简单链式查询
 * 暂时没有主从版本 用上了 再加
 * Class Tree
 * @package coco
 */
class Db
{
    use Singleton;

    private $mysql;
    private $options;
    /**
     * @var string
     */
    private $error = '';

    /**
     * @return string
     */
    public function getError(): string
    {
        return $this->error;
    }

    /**
     * @return string
     */
    public function _sql(): string
    {
        return $this->mysql->sql;
    }


    public function table(string $table): self
    {
        if ($table) {
            $this->options['table'] = $table;
        }
        return $this;
    }

    private function __construct()
    {
        $this->mysql = new Mysql();
        $this->options = [];
    }

    public function startTrans()
    {
        $this->mysql->startTrans();
    }

    public function rollBack()
    {
        $this->mysql->rollBack();
    }

    public function commit()
    {
        $this->mysql->commit();
    }

    public function transaction(callable $fn): bool
    {
        $this->startTrans();
        try {
            call_user_func($fn);
            $this->commit();
        } catch (\Exception $e) {
            $this->rollBack();
            $this->error = $e->getMessage();
            return false;
        }
        return true;
    }

    public function query(string $sql, array $param = [])
    {
        if (false === $result = $this->mysql->query($sql, $param)) {
            $this->error = $this->mysql->error . '(' . $this->mysql->errno . ')';
            return false;
        }
        return $result;
    }

    public function execute(string $sql, array $param = [])
    {
        if (false === $result = $this->mysql->execute($sql, $param)) {
            $this->error = $this->mysql->error . '(' . $this->mysql->errno . ')';
            return false;
        }
        return $result;
    }

    public function join(string $table, string $alias, string $on, string $type = null): self
    {
        if (is_null($type) || !in_array($type = strtoupper($type), ['LEFT', 'RIGHT', 'INNER'])) {
            $type = 'LEFT';
        }
        if (empty($this->options['join'])) {
            $this->options['join'] = [];
        }
        $this->options['join'][] = compact('table', 'alias', 'on', 'type');
        return $this;
    }

    public function where(array $where): self
    {
        if (empty($this->options['where'])) {
            $this->options['where'] = [];
        }
        if (!empty($where)) {
            $this->options['where'][] = $where;
        }
        return $this;
    }

    private function parseItemValue($value)
    {
        if (empty($value)) {
            return false;
        }
        if (is_string($value)) {
            $value = explode(',', $value);
        }
        if (!is_array($value)) {
            return false;
        }
        $item = [];
        foreach ($value as $in_value) {
            $item[':' . md5(uniqid($in_value))] = $in_value;
        }
        return $item;
    }

    public function field(string $field): self
    {
        $this->options['field'] = $field;
        return $this;
    }

    public function alias(string $alias): self
    {
        $this->options['alias'] = $alias;
        return $this;
    }

    public function limit(int $offset, int $size = null): self
    {
        if (empty($size)) {
            $size = $offset;
            $offset = 0;
        }
        $this->options['limit'] = compact('offset', 'size');
        return $this;
    }

    public function page(int $page, int $size = 10): self
    {
        if ($page < 1) {
            $page = 1;
        }
        return $this->limit(($page - 1) * $size, $size);
    }

    public function order(string $order): self
    {
        $this->options['order'] = $order;
        return $this;
    }

    public function group(string $group): self
    {
        $this->options['group'] = $group;
        return $this;
    }

    public function having(string $having): self
    {
        $this->options['having'] = $having;
        return $this;
    }

    public function buildSql(bool $build = true): self
    {
        $this->options['build_sql'] = $build;
        return $this;
    }


    private static function echo_sql(string $sql, array $params)
    {
        foreach ($params as $k => $v) {
            if (is_string($v)) {
                $v = '"' . addslashes($v) . '"';
            }
            $sql = str_replace($k, $v, $sql);
        }
        return $sql;
    }

    //最终
    public function value(string $key)
    {
        if (false === $result = $this->find()) {
            return false;
        }
        return $result[$key] ?? null;
    }

    public function column(string $key, string $index_key = null)
    {
        if (false === $result = $this->select()) {
            return false;
        }
        return array_column($result, $key, $index_key);
    }

    public function setOptions(array $options)
    {
        $this->options = array_merge($this->options, $options);
        return $this;
    }
    public function find(array $options = [])
    {
        $this->limit(1);
        $this->options = array_merge($this->options, $options);
        if (false === $options = $this->parseOptions()) {
            return false;
        }
        $sql = $this->buildSelectSql($options);
        $param = $options[':where'];
        if ($options['build_sql']) {
            return self::echo_sql($sql, $param);
        }
        if (false === $result = $this->mysql->query($sql, $param)) {
            $this->error = $this->mysql->error . '(' . $this->mysql->errno . ')';
            return false;
        }
        if (empty($result)) {
            return [];
        }
        return $result[0];
    }

    public function select(array $options = [])
    {
        $this->options = array_merge($this->options, $options);
        if (false === $options = $this->parseOptions()) {
            return false;
        }
        $sql = $this->buildSelectSql($options);
        $param = $options[':where'];
        if ($options['build_sql']) {
            return self::echo_sql($sql, $param);
        }
        if (false === $result = $this->mysql->query($sql, $param)) {
            $this->error = $this->mysql->error . '(' . $this->mysql->errno . ')';
            return false;
        }
        return $result;
    }

    /**
     * @param array $data_lists
     * @return false|int
     */
    public function insertAll(array $data_lists = [], string $insert = 'INSERT')
    {
        if (false === $options = $this->parseOptions()) {
            return false;
        }
        if (empty($data_lists)) {
            $this->error = '数据为空';
            return false;
        }
        //init
        $options['fields'] = [];
        $options['values'] = [];
        $options[':values'] = [];
        $values = [];
        foreach ($data_lists as $row) {
            if (empty($options['fields'])) {
                $options['fields'] = join(',', array_map(function ($v) {
                    return '`' . $v . '`';
                }, array_keys($row)));
            }
            $key_value = $this->parseItemValue($row);
            $values[] = join(',', array_keys($key_value));
            $options[':values'] = array_merge($options[':values'], $key_value);
        }
        $options['values'] = join(',', array_map(function ($v) {
            return '(' . $v . ')';
        }, $values));
        $sql = $this->buildInsertSql($options,$insert);
        $param = $options[':values'];
        if ($options['build_sql']) {
            return self::echo_sql($sql, $param);
        }
        if (false === $result = $this->mysql->execute($sql, $param)) {
            $this->error = $this->mysql->error . '(' . $this->mysql->errno . ')';
            return false;
        }
        return $result;
    }

    /**
     * @param array $data
     * @return false|int
     */
    public function insert(array $data, string $insert = 'INSERT')
    {
        if (false === $this->insertAll([$data], $insert)) {
            return false;
        }
        return (int)$this->mysql->insert_id;
    }

    public function update(array $data, array $options = [])
    {
        $this->options = array_merge($this->options, $options);
        if (empty($this->options['where'])) {
            $this->error = '请填写更新条件';
            return false;
        }
        if (false === $options = $this->parseOptions()) {
            return false;
        }
        if (empty($data)) {
            $this->error = '数据不能为空';
            return false;
        }
        $setfieldvalue = [];
        $options[':setfieldvalue'] = [];
        $fields = array_keys($data);
        $exp = [];
        foreach ($data as $k => &$item) {
            if (is_array($item)) {
                list($act, $item) = $item;
                if ('inc' === $act) {
                    $exp[$k] = "`$k`+";
                } elseif ('dec' === $act) {
                    $exp[$k] = "`$k`-";
                } else {
                    $exp[$k] = "`$k`$act";
                }
            }
        }
        unset($item);
        $options[':setfieldvalue'] = $kv = $this->parseItemValue($data);
        foreach ($fields as $k => $v) {
            $setfieldvalue[] = "`$v`=" . ($exp[$v] ?? '') . (array_keys($kv)[$k]);
        }
        $options['setfieldvalue'] = ' SET ' . join(',', $setfieldvalue);

        $sql = $this->buildUpdateSql($options);
        $param = array_merge($options[':setfieldvalue'], $options[':where']);
        if ($options['build_sql']) {
            return self::echo_sql($sql, $param);
        }
        if (false === $result = $this->mysql->execute($sql, $param)) {
            $this->error = $this->mysql->error . '(' . $this->mysql->errno . ')';
            return false;
        }
        return $result;
    }

    public function delete(array $options = [])
    {
        $this->options = array_merge($this->options, $options);
        if (empty($this->options['where'])) {
            $this->error = '请填写删除条件';
            return false;
        }

        if (false === $options = $this->parseOptions()) {
            return false;
        }
        $sql = $this->buildDeleteSql($options);
        $param = $options[':where'];
        if ($options['build_sql']) {
            return self::echo_sql($sql, $param);
        }
        if (false === $result = $this->mysql->execute($sql, $param)) {
            $this->error = $this->mysql->error . '(' . $this->mysql->errno . ')';
            return false;
        }
        return $result;
    }


    /**
     * @return array|false
     */
    protected function parseOptions()
    {
        $options = $this->options;
        $this->options = [];
        //table
        if (empty($options['table'])) {
            $this->error = 'table was miss of options';
            return false;
        }
        $table = $options['table'];
        $alias = $options['alias'] ?? '';
        $return = [];
        $return['table'] = " `$table`" . ($alias ? ' ' . $alias : '');
        $return['alias'] = $alias;

        //join
        $return['join'] = join('', array_map(function ($join) {
            return " {$join['type']} JOIN `{$join['table']}` {$join['alias']} on {$join['on']} ";
        }, $options['join'] ?? []));
        //field
        $return['field'] = ' ' . ($options['field'] ?? '*') . ' ';

        //where
        $where = $this->parseWhere($options['where'] ?? []);
        if (empty($where)) {
            $return['where'] = ' WHERE 1';
            $return[':where'] = [];
        } else {
            $return['where'] = ' WHERE ' . join(' AND ', $where['where']);
            $return[':where'] = $where[':where'] ?? [];
        }
        //order
        $return['order'] = empty($options['order']) ? '' : " ORDER BY {$options['order']}";
        //group
        $return['group'] = empty($options['group']) ? '' : " GROUP BY {$options['group']}";
        //having
        $return['having'] = empty($options['having']) ? '' : " HAVING {$options['having']}";

        //limit
        $return['limit'] = empty($options['limit']) ? '' : " LIMIT {$options['limit']['offset']},{$options['limit']['size']}";

        $return['build_sql'] = $options['build_sql'] ?? false;

        return $return;
    }

    protected function parseWhere(array $wheres): array
    {
        $return_where = [];
        foreach ($wheres as $where) {
            foreach ($where as $k => $v) {
                if ('_string' === $k) {
                    $return_where['where'][] = $v;
                    continue;
                }
                if (!is_array($v)) {
                    $v = ['=', $v];
                }

                $fields = array_unique(explode('|', $k));
                $or_item = [];
                $op = strtolower($v[0]);
                $value = $v[1] ?? null;
                foreach ($fields as $field) {
                    $_value = ':' . md5($field);
                    switch ($op) {
                        case 'eq':
                        case '=':
                            $or_item[] = "$field = $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'neq':
                        case '!=':
                        case '<>':
                            $or_item[] = "$field != $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'egt':
                        case '>=':
                            $or_item[] = "$field >= $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'gt':
                        case '>':
                            $or_item[] = "$field > $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'elt':
                        case '<=':
                            $or_item[] = "$field <= $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'lt':
                        case '<':
                            $or_item[] = "$field < $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'like':
                            $or_item[] = "$field LIKE $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'not like':
                            $or_item[] = "$field NOT LIKE $_value";
                            $return_where[':where'][$_value] = $value;
                            break;
                        case 'in':
                            if (false === $key_value = $this->parseItemValue($value)) {
                                break 2;
                            }
                            $keys = array_keys($key_value);
                            $return_where[':where'] = array_merge($return_where[':where'] ?? [], $key_value);
                            $or_item[] = "$field IN (" . join(',', $keys) . ")";
                            break;
                        case 'not in':
                            if (false === $key_value = $this->parseItemValue($value)) {
                                break 2;
                            }
                            $keys = array_keys($key_value);
                            $return_where[':where'] = array_merge($return_where[':where'] ?? [], $key_value);
                            $or_item[] = "$field NOT IN (" . join(',', $keys) . ")";
                            break;
                        case 'between':
                            if (false === $key_value = $this->parseItemValue($value)) {
                                break 2;
                            }
                            $keys = array_keys($key_value);
                            $return_where[':where'] = array_merge($return_where[':where'] ?? [], $key_value);
                            $or_item[] = "$field BETWEEN $keys[0] AND $keys[1]";
                            break;
                        case 'not between':
                            if (false === $key_value = $this->parseItemValue($value)) {
                                break 2;
                            }
                            $keys = array_keys($key_value);
                            $return_where[':where'] = array_merge($return_where[':where'] ?? [], $key_value);
                            $or_item[] = "$field NOT BETWEEN $keys[0] AND $keys[1]";
                            break;
                        case 'find in set':
                        case 'find_in_set':
                        case 'fis':
                            $or_item[] = "FIND_IN_SET('" . ((string)$value) . "',$field)";
                            break;
                        case 'not find in set':
                        case 'not_find_in_set':
                        case 'nfis':
                        case '!find_in_set':
                        case '!fis':
                            $or_item[] = "NOT FIND_IN_SET('" . ((string)$value) . "',$field)";
                            break;
                        case 'null':
                        case 'is null':
                            $or_item[] = "$field IS NULL";
                            break;
                        case 'not null':
                        case 'is not null':
                            $or_item[] = "$field IS NOT NULL";
                            break;
                    }
                }
                if ($or_item) {
                    $return_where['where'][] = '(' . join(' OR ', $or_item) . ')';
                }
            }
        }
        return $return_where;
    }

    protected function buildSelectSql(array $options)
    {
        $sql = "SELECT_field_FROM_table__join__where__group__having__order__limit_";
        return str_replace(
            [
                '_field_',
                '_table_',
                '_join_',
                '_where_',
                '_group_',
                '_having_',
                '_order_',
                '_limit_',
            ],
            [
                $options['field'],
                $options['table'],
                $options['join'],
                $options['where'],
                $options['group'],
                $options['having'],
                $options['order'],
                $options['limit'],
            ],
            $sql
        );
    }


    protected function buildInsertSql($options, $insert = 'INSERT')
    {
        if (empty($options['table'])) {
            $this->error = '`table` 缺失';
            return false;
        }
        $insert = strtoupper($insert);
        if (!in_array($insert, ['INSERT', 'REPLACE', 'IGNORE'])) {
            $insert = 'INSERT';
        }
        $sql = "$insert INTO_table_(_fields_) VALUES _values_";
        return str_replace(
            [
                '_table_',
                '_fields_',
                '_values_',
            ],
            [
                $options['table'],
                $options['fields'],
                $options['values'],
            ],
            $sql
        );
    }

    protected function buildUpdateSql(array $options)
    {
        $sql = "UPDATE_table__setfieldvalue__where__order__limit_";
        return str_replace(
            [
                '_table_',
                '_setfieldvalue_',
                '_where_',
                '_order_',
                '_limit_',
            ],
            [
                $options['table'],
                $options['setfieldvalue'],
                $options['where'],
                $options['order'],
                $options['limit'],
            ],
            $sql
        );
    }

    protected function buildDeleteSql(array $options)
    {
        $sql = "DELETE _alias_ FROM_table__where__order__limit_";
        return str_replace(
            [
                '_alias_',
                '_table_',
                '_where_',
                '_order_',
                '_limit_',
            ],
            [
                $options['alias'],
                $options['table'],
                $options['where'],
                $options['order'],
                $options['limit'],
            ],
            $sql
        );
    }
}
